package com.bk.cyxf;

import jt.classic.system.ISystem;
import jt.classic.system.database.SystemDataSource;


import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;

import org.limp.basework.SimpleBean4Table;

import org.limp.basework.console.XSystemConfig;
import org.limp.basework.impl.SimpleBean4TableImpl;
import org.limp.basework.tools.BaseworkUtil;


import java.sql.Connection;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.io.File;
  
import java.io.FileNotFoundException;   
import java.io.IOException;   
import java.io.InputStream;   
import java.util.ArrayList;   
   
import jxl.Cell;   
import jxl.Sheet;   
import jxl.Workbook;   
import jxl.read.biff.BiffException;
import org.limp.mine.EncryptTool;



public class ExceltojavaAction extends Action {
    public ActionForward execute(ActionMapping mapping, ActionForm form,
        HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        ActionForward forward = null;

        Connection conn = null;

        List<String> list_id = new ArrayList<String>(); 
        List<String> list_name = new ArrayList<String>();
        List<String> list_pwd = new ArrayList<String>();
        String REALPATH = (String) XSystemConfig.config().getProperty(XSystemConfig.XSYSTEM_REALPATH);
        File file = new File(REALPATH , "v_sys_user.xls");
        //String filePath = "D:/v_sys_user.xls";   
         
        Workbook workBook = null;

        try {
        	 //fs = new FileInputStream(filePath);   
        	 workBook = Workbook.getWorkbook(file);
        	 Sheet sheet = workBook.getSheet(0);//这里只取得第一个sheet的值，默认从0开始   
        	 /*System.out.println(sheet.getColumns());//查看sheet的列   
        	 System.out.println(sheet.getRows());//查看sheet的行*/        	 
        	 Cell cell = null;//就是单个单元格   
        	 
        	 for (int j = 0; j < sheet.getColumns(); j++) {   
                StringBuffer user_buffer_id =  new StringBuffer();
                StringBuffer user_buffer_name =  new StringBuffer();
                StringBuffer user_buffer_pwd =  new StringBuffer();
                HashMap my_user = new HashMap();
                for (int i = 0; i < sheet.getRows(); i++) {   
                     cell = sheet.getCell(j, i);
                     if(j==1){
                    	 user_buffer_id.append(cell.getContents());   
                    	 user_buffer_id.append(";");//将单元格的每行内容用逗号隔开
                    	 my_user.put("user_buffer_id", user_buffer_id);
                    	 
                     }
                     if(j==5){
                    	 user_buffer_name.append(cell.getContents());   
                    	 user_buffer_name.append(";");//将单元格的每行内容用逗号隔开
                    	 my_user.put("user_buffer_name", user_buffer_name);
                    	 
                     }
                     if(j==6){
                    	 user_buffer_pwd.append(cell.getContents());   
                    	 user_buffer_pwd.append(";");//将单元格的每行内容用逗号隔开
                    	 my_user.put("user_buffer_pwd", user_buffer_pwd);
                    	 
                     }
                     /*if(j==1 || j==5 || j==6){
                    	 user_buffer.append(cell.getContents());   
                    	 user_buffer.append(";");//将单元格的每行内容用逗号隔开
                     }*/
                        
                }   
                list_id.add(user_buffer_id.toString());//将每行的字符串用一个String类型的集合保存。
                list_name.add(user_buffer_name.toString());//将每行的字符串用一个String类型的集合保存。
                list_pwd.add(user_buffer_pwd.toString());//将每行的字符串用一个String类型的集合保存。
                
            }   
            workBook.close();//记得关闭 
            
            //迭代集合查看每行的数据
            String[] list_id_array = list_id.get(1).toString().split(";");
            String[] list_name_array = list_name.get(5).toString().split(";");
            String[] list_pwd_array = list_pwd.get(6).toString().split(";");
            BaseworkUtil util = new BaseworkUtil();
            SystemDataSource ds = SystemDataSource.getInstance();
            conn = ds.getConnection();
            for(int a=0;a<list_id_array.length;a++){
            	SimpleBean4Table sbt1 = new SimpleBean4TableImpl("ISYS_USER");
            	SimpleBean4Table sbt2 = new SimpleBean4TableImpl("ISYS_DEPT_USER");
            	
                sbt1.getResource().put("uname", list_name_array[a]);
                sbt1.getResource().put("logid", list_id_array[a]);
                sbt1.getResource().put("pwd", EncryptTool.encryptString(list_pwd_array[a]));
                sbt1.getResource().put("job", "08");
                sbt1.getResource().put("unumber", "");
                sbt1.getResource().put("uphone", "");
                sbt1.getResource().put("uemail", "");
                sbt1.getResource().put("utype", "");
                sbt1.getResource().put("sex", "男");
                sbt1.getResource().put("birthday", "");
                sbt1.getResource().put("flag", 0);
                sbt1.getResource().put("rating", 5);
                sbt1.getResource().put("disporder", 99);

                Map m1 = util.manualSave1(sbt1, conn);

                sbt2.getResource().put("gid", "00000001");
                sbt2.getResource().put("uid", (String) m1.get("ID"));
                sbt2.getResource().put("upt", 0);
                sbt2.getResource().put("upj", "08");
                sbt2.getResource().put("upr", 5);
                sbt2.getResource().put("upo", 99);
                
                Map m2 = util.manualSave1(sbt2, conn);
                
            
            	
            }
            conn.commit();
            
            /*System.out.println("aaa_id==="+list_id.get(1).toString());
            System.out.println("aaa_name==="+list_name.get(5).toString());
            System.out.println("aaa_pwd==="+list_pwd.get(6).toString());*/
        }
        catch (FileNotFoundException e) {   
            e.printStackTrace();   
        } 
        catch (BiffException e) {   
               e.printStackTrace();   
        } 
        catch (IOException e) {   
               e.printStackTrace();   
        } 
        catch (Exception ex) {
            ISystem.catchException(request, this, ex);
            forward = mapping.findForward("error");
        } finally {
            SystemDataSource.closeConnection(conn);
        }

        return forward;
    }
}
